// KQL Explanation

//1. (double) click on table name 

AzureDiagnostics
// this is like saying SELECT * FROM AzureDiagnostics WHERE 1=1


// 2. Add a where clause. 
// Note you need to add "|" like in PowerShell
// but the equaility is like in c#

AzureDiagnostics
| where Category == 'SQLSecurityAuditEvents'
// this is like saying SELECT * FROM AzureDiagnostics WHERE Category = 'SQLSecurityAuditEvents'


// 3. We can add more where clauses
AzureDiagnostics
| where Category == 'SQLSecurityAuditEvents'
| where TimeGenerated > ago(12h)

// OR

AzureDiagnostics
| where Category == 'SQLSecurityAuditEvents' and  TimeGenerated > ago(12h)

// 4.  Beware. KQL is case sensitive
AzureDiagnostics
| where application_name_s == "dbatools PowerShell module - dbatools.io"

// this return 0 rows
AzureDiagnostics
| where application_name_s == "DBATOOLS PowerShell module - dbatools.io"

// this remove the case sensitivity
AzureDiagnostics
| where application_name_s =~ "DBATOOLS PowerShell module - dbatools.io"

// contains is case insensitive 
AzureDiagnostics
| where application_name_s contains "DBATOOLS"


// 5. limit the columns returned
AzureDiagnostics
| where Category == 'SQLSecurityAuditEvents'
| where TimeGenerated > ago(24h)
| project
    event_time_t,
    statement_s,
    succeeded_s,
    affected_rows_d,
    server_principal_name_s,
    client_ip_s,
    application_name_s,
    additional_information_s,
    data_sensitivity_information_s
	
// 6. order the data
AzureDiagnostics
| where Category == 'SQLSecurityAuditEvents'
| where TimeGenerated > ago(24h)
| project
    event_time_t,
    statement_s,
    succeeded_s,
    affected_rows_d,
    server_principal_name_s,
    client_ip_s,
    application_name_s,
    additional_information_s,
    data_sensitivity_information_s	
| order by event_time_t desc

// 7. Limit the number of rows returned
AzureDiagnostics
| where Category == 'SQLSecurityAuditEvents'
| where TimeGenerated > ago(24h)
| project
    event_time_t,
    statement_s,
    succeeded_s,
    affected_rows_d,
    server_principal_name_s,
    client_ip_s,
    application_name_s,
    additional_information_s,
    data_sensitivity_information_s
| order by affected_rows_d desc    
| take 10	


// 8 New query
// Create variables for the query

let start = ago(24h);
let end = now();

AzureMetrics
| where ResourceProvider == "MICROSOFT.SQL" and Resource != "MASTER"
| where TimeGenerated > start and TimeGenerated < end
| where MetricName == "cpu_percent"

// 9 Group By
let start = ago(24h);
let end = now();
let timeGrain=5m;

AzureMetrics
| where ResourceProvider == "MICROSOFT.SQL" and Resource != "MASTER"
| where TimeGenerated > start and TimeGenerated < end
| where MetricName == "cpu_percent"
| summarize avg(Average) by Resource, bin(TimeGenerated, max_of(timeGrain, 1m))

// 10 create a graph of the results
let start = ago(24h);
let end = now();
let timeGrain=5m;

AzureMetrics
| where ResourceProvider == "MICROSOFT.SQL" and Resource != "MASTER"
| where TimeGenerated > start and TimeGenerated < end
| where MetricName == "cpu_percent"
| summarize avg(Average) by Resource, bin(TimeGenerated, max_of(timeGrain, 1m))
| render timechart 

// 11 New Query
// find the top 5 max values
AzureMetrics
| where ResourceProvider == "MICROSOFT.SQL" and Resource != "MASTER"
| where TimeGenerated > ago(4h)
| where MetricName == "cpu_percent"
| top 5 by Maximum
|project TimeGenerated,Maximum,Minimum


// What metrics are available 
// select DISTINCT
AzureMetrics
| distinct MetricName

// Select COUNT
AzureMetrics 
| count